﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Data;

namespace DataProvider
{
    public class UserDataProvider
    {

        public UserDataProvider()
        { 
        
        }

        public int GetUserSecurityLevel(User user)
        {
            SqlConnection conexao = Connection.GetConnection();
            int securityLevel = -1;
            try
            {
                SqlCommand cmd = new SqlCommand("GetUserClass", conexao);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@sLogin", user.Login);
                cmd.Parameters.AddWithValue("@sPassword", user.Password);
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {                    
                        securityLevel = int.Parse(dr["iClasse"].ToString());
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                conexao.Close();
            }
            return securityLevel;
           
        }

        public List<Table> GetUserViewTable(User iUser)
        {
            SqlConnection conexao = Connection.GetConnection();
            List<Table> returnList = new List<Table>();
            try
            {
                SqlCommand cmd = new SqlCommand("GetUserViewTable", conexao);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@iClassUser", iUser.Classe);
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        Table tempTable = new Table();
                        tempTable.STable = dr["sNome"].ToString();
                        tempTable.IdTable = int.Parse(dr["Idtable"].ToString());
                        if (dr["iSeguranca"] != DBNull.Value)
                        {
                            tempTable.Classe = int.Parse(dr["iSeguranca"].ToString());
                        }
                        tempTable.STipoSeguranca = dr["sTipo"].ToString(); 

                        returnList.Add(tempTable);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                conexao.Close();
            }
            return returnList;

        }

        public TableData GetViewTable2(Table iTable)
        {
            SqlConnection conexao = Connection.GetConnection();
            List<Table> returnList = new List<Table>();
            TableData aTableData = new TableData();
            try
            {
                SqlCommand cmd = new SqlCommand("GetViewTable", conexao);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@sTableName", iTable.STable);

                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    int tamanho = dr.FieldCount;
                    int contador = 0;
                    for (int i = 0; i < tamanho; i++)
                    {
                        string temp = dr.GetName(i);
                        aTableData.Colunas.Add(temp);
                        temp = dr.GetFieldType(i).ToString();
                        aTableData.TipoColuna.Add(temp);
                    }

                    while (dr.Read())
                    {
                        if (iTable.STipoSeguranca.Equals("tabela"))
                        {
                            for (int i = 0; i < tamanho; i++)
                            {
                                if (dr[i] != DBNull.Value)
                                {
                                    aTableData.Valores.Add(dr[i].ToString());
                                }
                                else
                                {
                                    aTableData.Valores.Add("");
                                }
                            }
                        }
                        else if (iTable.STipoSeguranca.Equals("registro"))
                        {
                           
                                for (int i = 0; i < tamanho; i++)
                                {
                                    aTableData.Valores.Add(dr[i].ToString());
                               
                            }
                        }
                        else
                        {
                            for (int i = 0; i < tamanho; i = i + 2)
                            {
                                if (dr[i] != DBNull.Value && dr[i + 1] != DBNull.Value)
                                {
                                    
                                        aTableData.Valores.Add(dr[i].ToString());
                                        aTableData.Valores.Add(dr[i + 1].ToString());
                                   
                                }
                                else
                                {
                                    aTableData.Valores.Add("");
                                    aTableData.Valores.Add("");
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                conexao.Close();
            }
            return aTableData;

        }

        public List<Table> GetUserViewTableAlter(User iUser)
        {
            SqlConnection conexao = Connection.GetConnection();
            List<Table> returnList = new List<Table>();
            try
            {
                SqlCommand cmd = new SqlCommand("GetUserViewTableForAlter", conexao);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@iClassUser", iUser.Classe);
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        Table tempTable = new Table();
                        tempTable.STable = dr["sNome"].ToString();
                        tempTable.IdTable = int.Parse(dr["Idtable"].ToString());
                        if (dr["iSeguranca"] != DBNull.Value)
                        {
                            tempTable.Classe = int.Parse(dr["iSeguranca"].ToString());
                        }
                        tempTable.STipoSeguranca = dr["sTipo"].ToString();

                        returnList.Add(tempTable);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                conexao.Close();
            }
            return returnList;

        }

        public TableData GetViewTable(User iUser, Table iTable)
        {
            SqlConnection conexao = Connection.GetConnection();
            List<Table> returnList = new List<Table>();
            TableData aTableData = new TableData();
            try
            {
                SqlCommand cmd = new SqlCommand("GetViewTable", conexao);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@sTableName", iTable.STable);

                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    int tamanho = dr.FieldCount;
                    int contador = 0;
                    for (int i = 0; i < tamanho; i++)
                    {
                        string temp = dr.GetName(i);
                        aTableData.Colunas.Add(temp);
                        temp = dr.GetFieldType(i).ToString();
                        aTableData.TipoColuna.Add(temp);
                    }

                    while (dr.Read())
                    {
                        if (iTable.STipoSeguranca.Equals("tabela"))
                        {
                            for (int i = 0; i < tamanho; i++ )
                            {
                                if (dr[i] != DBNull.Value)
                                {
                                    aTableData.Valores.Add(dr[i].ToString());
                                }
                                else
                                {
                                    aTableData.Valores.Add("");
                                }
                            }
                        }
                        else if (iTable.STipoSeguranca.Equals("registro"))
                        {
                            if (int.Parse(dr["iClasseLinha"].ToString()) <= iUser.Classe)
                            {
                                for (int i = 0; i < tamanho; i++)
                                {
                                    if (dr[i] != DBNull.Value)
                                    {
                                        aTableData.Valores.Add(dr[i].ToString());
                                    }
                                    else
                                    {
                                        aTableData.Valores.Add("");
                                    }
                                }
                            }
                        }
                        else
                        {
                            for (int i = 0; i < tamanho; i = i +2)
                            {
                                if (dr[i] != DBNull.Value && dr[i+1] != DBNull.Value)
                                {
                                    if (int.Parse(dr[i + 1].ToString()) <= iUser.Classe)
                                    {
                                        aTableData.Valores.Add(dr[i].ToString());
                                        aTableData.Valores.Add(dr[i+1].ToString());
                                    }
                                    else
                                    {
                                        aTableData.Valores.Add("");
                                        aTableData.Valores.Add("");
                                    }                                    
                                }
                                else
                                {
                                    aTableData.Valores.Add("");
                                    aTableData.Valores.Add("");
                                }
                            }
                            
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                
                throw ex;
            }

            finally
            {
                conexao.Close();
            }
            return aTableData;

        }

        public void InsertTable(Table iTable, string iValores)
        {
            SqlConnection conexao = Connection.GetConnection();
            try
            {
                SqlCommand cmd = new SqlCommand("InsertTable", conexao);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@sTable", iTable.STable);
                cmd.Parameters.AddWithValue("@sValores", iValores);
                cmd.ExecuteNonQuery();

                
            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                conexao.Close();
            }

        }

        public void DeleteTable(Table iTable, List<string> iValores)
        {
            SqlConnection conexao = Connection.GetConnection();
            List<Table> returnList = new List<Table>();
            try
            {
                SqlCommand cmd = new SqlCommand("DeleteTable", conexao);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@sColunaId", iValores[0]);
                cmd.Parameters.AddWithValue("@iId", iValores[1]);
                cmd.Parameters.AddWithValue("@sOpicional1", iValores[2]);
                cmd.Parameters.AddWithValue("@sOpicional2", iValores[3]);
                cmd.Parameters.AddWithValue("@sTabela", iTable.STable);
                cmd.ExecuteNonQuery();
                

            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                conexao.Close();
            }

        }

        public List<User> GetUser(User user)
        {
            List<User> retorno = new List<User>();
            SqlConnection conexao = Connection.GetConnection();

            try
            {
                SqlCommand cmd = new SqlCommand("GetUsers", conexao);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@IdUser", user.IdUser);
                cmd.Parameters.AddWithValue("@Login", user.Login);
                cmd.Parameters.AddWithValue("@Classe", user.Classe);

                using (SqlDataReader dr = cmd.ExecuteReader())
                {

                    while (dr.Read())
                    {
                        User temp = new User();
                        temp.IdUser = int.Parse(dr["Id"].ToString());
                        temp.Login = dr["Login"].ToString();
                        temp.Password = dr["Password"].ToString();
                        temp.Classe = int.Parse(dr["Classe"].ToString());

                        retorno.Add(temp);
                    }
                }

                return retorno;

            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                conexao.Close();
            }
        }


    }
}
